var createError = require('http-errors');
var express = require('express');
var path = require('path');
var cookieParser = require('cookie-parser');
var logger = require('morgan');
var bodyParse = require('body-parser');
const nodemailer = require("nodemailer");//邮件相关
const { v4: uuidv4 } = require('uuid');

const fs = require('fs')
const Busboy = require('busboy')
var dbconfig = require('./dbconfig.js');
// var psql = new pg.Pool(dbconfig);
// var router = express.Router();
//数据库相关
var mysql = require("mysql");

var connection = mysql.createConnection(dbconfig);
connection.connect();
//
var bodyParser = require('body-parser');

var app = express();
app.use(bodyParser.urlencoded({ extended: false }));
app.use(bodyParser.json());
// view engine setup
app.set('views', path.join(__dirname, 'views'));
app.set('view engine', 'jade');

app.use(logger('dev'));
app.use(express.json());
app.use(express.urlencoded({ extended: false }));
app.use(cookieParser());
app.use(express.static(path.join(__dirname, 'public')));

// app.use('/', indexRouter);
// app.use('/users', usersRouter);
//跨域
app.use((req, res, next) => {
  // res.set('X-Content-Type-Options', 'nosniff');
  // res.set('Cache-Control', 'no-store');
  // res.set('X-XSS-Protection', '1; mode=block');
  // res.set('Strict-Transport-Security', '3600');
  res.set('access-control-allow-origin', '*');
  res.set('access-control-allow-methods',
    ['GET', 'POST', 'PUT', 'DELETE', 'OPTIONS']);
  res.header('Access-Control-Allow-Headers', 'Content-Type, Content-Length, Authorization, Accept, X-Requested-With , yourHeaderFeild');

  // OPTIONS类型的请求 复杂请求的预请求
  if (req.method == 'OPTIONS') {
    res.send(200)
  } else {
    /*让options请求快速返回*/
    next()
  }
  // next();
});


//==========================================上传图片=============================
// 设置图片存储路径
//开放静态文件/uploads
app.use('/uploads/', express.static('./uploads/'));
//头像文件放到upload/icon
app.post('/upload/icon', async (req, res) => {
  const busboy = new Busboy({ headers: req.headers })
  // 监听文件解析事件
  busboy.on('file', (fieldname, file, filename, encoding, mimetype) => {
    const saveTo = path.join(__dirname, 'uploads', 'icon', filename);
    file.pipe(fs.createWriteStream(saveTo));
    // 解析文件结束
    // file.on('end', function () {
    // console.log(`File  Finished`)
    // res.send(saveTo)
    // })
  })
  // 监听请求中的字段
  // busboy.on('field', function (fieldname, val, fieldnameTruncated, valTruncated) {
  // console.log(`Field [${fieldname}]: value: ${inspect(val)}`)
  // console.log(`Field `)
  // })
  // 监听结束事件
  busboy.on('finish', function () {
    // res.writeHead(303, { Connection: 'close', Location: '/' })
    res.status(200).send('ok')
  })
  req.pipe(busboy)
  // res.status(200).send('ok')
  // return;
})

//房子图片文件放到upload/pic
app.post('/upload/pic', async (req, res) => {
  const busboy = new Busboy({ headers: req.headers })
  // 监听文件解析事件
  busboy.on('file', (fieldname, file, filename, encoding, mimetype) => {
    // console.log(fieldname, file, filename, encoding, mimetype)
    const saveTo = path.join(__dirname, 'uploads', 'pic', filename);
    // console.log("saveTo", saveTo)
    file.pipe(fs.createWriteStream(saveTo));

    // 解析文件结束
    // file.on('end', function () {
    // console.log(`File  Finished`)
    // res.send(saveTo)
    // })
  })

  // 监听请求中的字段
  // busboy.on('field', function (fieldname, val, fieldnameTruncated, valTruncated) {
  // console.log(`Field [${fieldname}]: value: ${inspect(val)}`)
  // console.log(`Field `)
  // })

  // 监听结束事件
  busboy.on('finish', function () {
    // console.log('Done parsing form!')
    // res.writeHead(303, { Connection: 'close', Location: '/' })
    res.status(200).send('ok')
    // console.log("Finished")

    // res.end()
  })
  req.pipe(busboy)
  // res.status(200).send('ok')
  // return;
})

//其他图片文件放到upload/other
app.post('/upload/other', async (req, res) => {
  const busboy = new Busboy({ headers: req.headers })
  // 监听文件解析事件
  busboy.on('file', (fieldname, file, filename, encoding, mimetype) => {
    // console.log(fieldname, file, filename, encoding, mimetype)
    const saveTo = path.join(__dirname, 'uploads', 'other', filename);
    // console.log("saveTo", saveTo)
    file.pipe(fs.createWriteStream(saveTo));

    // 解析文件结束
    // file.on('end', function () {
    // console.log(`File  Finished`)
    // res.send(saveTo)
    // })
  })

  // 监听请求中的字段
  // busboy.on('field', function (fieldname, val, fieldnameTruncated, valTruncated) {
  // console.log(`Field [${fieldname}]: value: ${inspect(val)}`)
  // console.log(`Field `)
  // })

  // 监听结束事件
  busboy.on('finish', function () {
    // console.log('Done parsing form!')
    // res.writeHead(303, { Connection: 'close', Location: '/' })
    res.status(200).send('ok')
    // console.log("Finished")

    // res.end()
  })
  req.pipe(busboy)
  // res.status(200).send('ok')
  // return;
})
//===================================登录=======================================
app.post('/login', async function (req, res, next) {
  let user = req.body.username;//*
  let passwd = req.body.password;//*
  await connection.query(`select passwd from user where name="${user}";`, function (err, result, fields) {
    console.log(err, result, fields)
    if (result[0]) {
      if (result[0].passwd == passwd) {
        res.status(200);
        res.send("ok")
      }
    } else {
      res.status("400").send({ msg: "用户名密码错误！" })
    }
  })
})


//===================================用户表=======================================
app.get('/users', async function (req, res, next) {
  await connection.query(`select * from user;`, function (err, result, fields) {
    console.log(err, result, fields)
    if (err) throw err;
    res.send(result)
  })
})

app.post('/adduser', async function (req, res, next) {
  let name = JSON.stringify(req.body.name);//*
  let role = req.body.role || 1;//默认租户
  let passwd = JSON.stringify(req.body.passwd);//*
  let phone = req.body.phone || 0;
  let money = req.body.money || 0;
  let email = JSON.stringify(req.body.email) || "";
  let img = JSON.stringify(req.body.img) || JSON.stringify("/uploads/xuan4.png");
  console.log(req)
  console.log(req.body)
  console.log(req.body.email)
  //常见错误insertid没有，result为undefined==>所传用户信息必须用""包裹，通过在下面的function中打印err
  await connection.query(`insert into user (name,passwd,role,phone,money,img,email) values(${name},${passwd},${role},${phone},${money},${img},${email});`,
    function (err, result, fields) {
      console.log(err, result, fields)
      if (result.insertId) {
        res.status(200);
        res.send("ok")
      } else {
        res.status("400").send({ msg: "添加错误！", err: err })
      }
    })
})

app.delete('/deluser', async function (req, res, next) {
  let id = req.body.id;//   ** 
  await connection.query(`delete from user where id=${id};`,
    function (err, result, fields) {
      console.log(err, result, fields)
      if (result.affectedRows == 1) {
        res.status(200);
        res.send("ok")
      } else {
        res.status("400").send({ msg: "删除错误！", err: err })
      }
    })
})

app.post('/updateuser', async function (req, res, next) {
  let id = req.body.id;   //    **
  await connection.query(`select * from user where id=${id}`,
    async function (err, result, fields) {
      let name = JSON.stringify(req.body.name) || JSON.stringify(result[0].name);
      let role = req.body.role || result[0].role;
      let passwd = JSON.stringify(req.body.passwd) || JSON.stringify(result[0].passwd);
      let phone = req.body.phone || result[0].phone;
      let money = req.body.money || result[0].money;
      let img = JSON.stringify(req.body.img) || JSON.stringify(result[0].img);
      await connection.query(`update user SET name=${name},passwd=${passwd},role=${role},phone=${phone},money=${money},img=${img} where id=${id};`,
        function (err, result, fields) {
          console.log(err, result, fields)
          if (result.affectedRows == 1) {
            res.status(200);
            res.send("ok")
          } else {
            res.status("400").send({ msg: "更改错误！", err: err })
          }
        })

    })

})

//忘记密码
app.post('/getpasswd', async function (req, res, next) {
  let u = {};
  let email = JSON.stringify(req.body.email);   //    **
  //确认该用户是否存在
  await connection.query(`select * from user where email=${email};`,
    async function (err, result, fields) {
      if (result.length == 1) {
        u = result[0];
        //修改数据库
        const newpasswd = JSON.stringify(uuidv4().slice(0, 8));
        await connection.query(`update user SET passwd=${newpasswd} where id=${u.id};`,
          function (err, result, fields) {
            console.log(err, result)
            if (result.affectedRows == 1) {
              res.status(200);
              res.send("密码修改成功")
            } else {
              res.status(400).send({ msg: "更改错误！", err: err })
            }
          })
        //给用户发送邮件
        let transporter = nodemailer.createTransport({
          service: 'qq',
          port: 456,
          secureConnection: true,
          auth: {
            user: '3369148892@qq.com',
            pass: 'xebpzilyrmiacgjg',
          }
        });

        let mailOptions = {
          from: '3369148892@qq.com',
          to: email,
          subject: `您好，${u.name}的密码已重置为${newpasswd}，请妥善保管您的密码！`,
          html: `<b>您好，${u.name}的密码已重置为${newpasswd}，请妥善保管您的密码！</b>`
        };

        transporter.sendMail(mailOptions, (err, data) => {
          if (err) {
            console.log(err);
            res.status("402").send({ msg: "发送失败", err: err })
          } else {
            console.log(data);
            res.status(201).send({ msg: "发送成功", err: err })
          }
        })
      } else {
        res.status("401").send({ msg: "用户不存在！", err: err })
      }
    })


})
//===================================民宿表=======================================
app.get('/houses', async function (req, res, next) {
  await connection.query(`select * from house;`, function (err, result, fields) {
    if (err) throw err;
    res.send(result)
  })
})
app.get('/house', async function (req, res, next) {
  let id = req.query.id;
  await connection.query(`select * from house where id=${id};`, function (err, result, fields) {
    console.log(err, result, fields)
    if (err) throw err;
    res.send(result)
  })
})

app.post('/addhouse', async function (req, res, next) {
  let host_id = req.body.host_id || 2;//*
  let name = JSON.stringify(req.body.name);//*
  let type = JSON.stringify(req.body.type);//*
  let num = req.body.num || 2;
  let description = JSON.stringify(req.body.description) || "整套房源";
  let p = req.body.p || 00;
  let wifi = req.body.wifi || 00;
  let reception = req.body.reception || 00;
  let grade = req.body.grade || 1;
  let money = req.body.money || 1;
  await connection.query(`select role from user where id=${host_id}`,
    async function (err, result, fields) {
      console.log(err, result, fields)
      if (result[0].role !== 1) {
        res.status(400).send({ msg: "上传人类型不是房东" })
      } else {
        await connection.query(`insert into house (host_id,name,type,num,description,p,wifi,reception,grade,money) values(${host_id},${name},${type},${num},${description},${p},${wifi},${reception},${grade},${money});`,
          function (err, result, fields) {
            console.log(err, result, fields)
            if (result.insertId) {
              res.status(200);
              res.send("ok")
            } else {
              res.status(401).send({ msg: "添加错误！", err: err })
            }
          })
      }
    })
})
app.post('/updatehouse', async function (req, res, next) {
  let id = req.body.id;   //    **
  await connection.query(`select * from house where id=${id}`,
    async function (err, result, fields) {
      let host_id = req.body.host_id || result[0].host_id;
      let name = JSON.stringify(req.body.name) || JSON.stringify(result[0].name);
      let type = JSON.stringify(req.body.type) || JSON.stringify(result[0].type);
      let num = req.body.num || result[0].num;
      let description = JSON.stringify(req.body.description) || JSON.stringify(result[0].description);
      let p = req.body.p || result[0].p;
      let wifi = req.body.wifi || result[0].wifi;
      let reception = req.body.reception || result[0].reception;
      let grade = req.body.grade || result[0].grade;
      let money = req.body.money || result[0].money;
      await connection.query(`update house SET host_id=${host_id},name=${name},type=${type},num=${num},description=${description},p=${p},wifi=${wifi},reception=${reception},grade=${grade},money=${money} where id=${id};`,
        function (err, result, fields) {
          console.log(err, result, fields)

          if (result.affectedRows == 1) {
            res.status(200);
            res.send("ok")
          } else {
            res.status("400").send({ msg: "更改错误！", err: err })
          }
        })

    })

})
app.delete('/delhouse', async function (req, res, next) {
  let id = req.body.id;//    **

  await connection.query(`delete from house where id=${id};`,
    function (err, result, fields) {
      if (result.affectedRows == 1) {
        res.status(200);
        res.send("ok")
      } else {
        res.status("400").send({ msg: "删除错误！", err: err })
      }
    })
})
//=============================房子图片表=====================

app.get('/housepics', async function (req, res, next) {
  await connection.query(`select * from pic;`, function (err, result, fields) {
    if (err) throw err;
    res.send(result)
  })
})
//获取某房子的图片
app.get('/housepic', async function (req, res, next) {
  let id = req.query.id;//房子
  await connection.query(
    `select * from pic where house_id=${id};`,
    function (err, result, fields) {
      // console.log(result)
      if (err) throw err;
      res.send(result)
    })
})
app.post('/addhousepic', async function (req, res, next) {
  let id = req.body.id;//*
  let img = JSON.stringify(req.body.img);//**
  await connection.query(`insert into pic (house_id,url) values (${id},${img});`,
    function (err, result, fields) {
      console.log(err, result, fields)

      if (result.insertId) {
        res.status(200);
        res.send("ok")
      } else {
        res.status("400").send({ msg: "添加错误！", err: err })
      }
    })
})
app.delete('/delhousepic', async function (req, res, next) {
  let id = req.body.id;//    **

  await connection.query(`delete from pic where id=${id};`,
    function (err, result, fields) {
      if (result.affectedRows == 1) {
        res.status(200);
        res.send("ok")
      } else {
        res.status("400").send({ msg: "删除错误！", err: err })
      }
    })
})


//===================================订单表=======================================
//订单表没有删除
//0待付款，1待入驻，2已确认，3已取消
app.get('/list', async function (req, res, next) {
  await connection.query(`select * from list;`, function (err, result, fields) {
    if (err) throw err;
    res.send(result)
  })
})

//查询某个用户订单列表
app.get('/userlist', async function (req, res, next) {
  let id = req.query.id;//*
  await connection.query(
    `select list.id,list.host_id,list.user_id,list.house_id,list.status,list.in_date,list.out_date,list.money,house.name,house.type,house.num,house.description from list ,user, house where user.id = list.user_id and house.id = list.house_id and list.user_id=${id};`,
    // `select * from list where user_id=${id};`,
    function (err, result, fields) {
      console.log(err, result, fields)
      if (err) throw err;
      res.send(result)
    })
})

app.post('/addlist', async function (req, res, next) {
  let host_id = req.body.host_id;//*
  let user_id = req.body.user_id;//*
  let house_id = req.body.house_id;//*
  let status = req.body.status || 0;
  let in_date = req.body.in_date; //*
  let out_date = req.body.out_date;//*
  let money = req.body.money;//*

  await connection.query(`insert into list (host_id,user_id,house_id,status,in_date,out_date,money) values(${host_id},${user_id},${house_id},${status},${in_date},${out_date},${money});`,
    function (err, result, fields) {
      console.log(err, result, fields)
      if (result.insertId) {
        res.status(200);
        res.send("ok")
      } else {
        res.status("400").send({ msg: "添加错误！", err: err })
      }
    })
})

//修改订单状态
app.post('/updatelist', async function (req, res, next) {
  let id = req.body.id || 3;//    **
  let user_id = req.body.user_id || 3;//    **
  let status = req.body.status || 0;//    **
  //0待付款，1待入驻，2已确认，3已取消。
  await connection.query(`update list SET status=${status} where id=${id};`,
    async function (err, result, fields) {
      if (result.affectedRows == 1) {
        res.status(200);
        if (status == 0) {
          await connection.query(`insert into msg (user_id,msg) values (${user_id},'请及时付款。');`, function (err, result, fields) {
            res.status(200);
            res.send("请及时付款")
          })
        } else if (status == 1) {
          await connection.query(`insert into msg (user_id,msg) values (${user_id},'已付款，待入住。');`, function (err, result, fields) {
            res.status(200);
            res.send("已付款，待入住。")
          })
        } else if (status == 2) {
          await connection.query(`insert into msg (user_id,msg) values (${user_id},'已确认入住。');`, function (err, result, fields) {
            res.status(200);
            res.send("已确认入住。")
          })
        } else {
          await connection.query(`insert into msg (user_id,msg) values (${user_id},'订单已取消。');`, function (err, result, fields) {
            res.status(200);
            res.send("订单已取消。")
          })
        }
      } else {
        res.status("400").send({ msg: "更改错误！", err: err })
      }
    })
})
//-----------------------------消息表--------------------------
app.get('/msgs', async function (req, res, next) {
  let id = req.query.id;//*
  await connection.query(
    `select * from msg;`,
    function (err, result, fields) {
      if (err) throw err;
      res.send(result)
    })
})
//获取某个信息
app.get('/msg', async function (req, res, next) {
  let id = req.query.id;//*
  await connection.query(
    `select * from msg where user_id=${id};`,
    function (err, result, fields) {
      if (err) throw err;
      res.send(result)
    })
})



















// catch 404 and forward to error handler
app.use(function (req, res, next) {
  next(createError(404));
});

// error handler
app.use(function (err, req, res, next) {
  // set locals, only providing error in development
  res.locals.message = err.message;
  res.locals.error = req.app.get('env') === 'development' ? err : {};

  // render the error page
  res.status(err.status || 500);
  res.render('error');
});

module.exports = app;